package com.F03.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F02.actionform.F020106ActionForm;
import com.F03.actionform.F0301ActionForm;
import com.F03.actionform.F0302ActionForm;
import com.F03.actionform.F0303ActionForm;
import com.F03.info.PdPlanningInfo;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.util.CommonTools;

public class PdPlanningDAO extends DataOperations{
	Logger log = Logger.getLogger(PdPlanningDAO.class);
	public PdPlanningDAO(){
		
	}
    public PdPlanningDAO(Connection connection){
        super(connection);
    }
    
    public List getCollectionList(){
    	List list=new ArrayList();
		String sql="SELECT code, phases FROM t_critical_phases ";
		Map map=null;
		ResultSet rest=null;
		try{
			log.info(sql.toString());
			rest=super.executeQuery(sql);
			while(rest.next()){
				map=new HashMap();
				map.put("CO",CommonTools.null2String(rest.getString("code")));
				map.put("NA",CommonTools.null2String(rest.getString("phases")));
				list.add(map);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Dispose.dispose(rest);
		}
		return list;
    }
    
    public String getBeginDate(String manufacturingOrder,String finishDate) throws SelectException{
    	String beginDate ="";
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select (CONVERT(datetime,'"+finishDate+"')-max(b.finish_days)+1) as begin_date ") ;
    	sql.append(" from t_not_standard_products a ,t_family b ");
    	sql.append(" where a.manufacturing_order ='"+manufacturingOrder+"' and a.family_code = b.family_code ");
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		if(finishDate.equals("")){
    			return beginDate;
    		}else{
    			rest = this.executeQuery(sql.toString());
        		while(rest.next()){
        			beginDate = CommonTools.getDateTime(rest.getDate("begin_date"));
        		}
    		}
 
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return beginDate;
    }
    
    public String getModelQuantity(String manufacturingOrder) throws SelectException{
    	String reValue = "";
    	String model = "";
    	String quantity ="";
    	String familyCode ="";
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select a.model,a.quantity,b.family_code from c_orders a,t_not_standard_products b ");
    	sql.append(" where a.manufacturing_order ='"+manufacturingOrder+"' ");
    	sql.append(" and a.manufacturing_order = b.manufacturing_order ");
   
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			model = CommonTools.null2String(rest.getString("model"));
    			quantity = CommonTools.null2String(rest.getString("quantity"));
    			familyCode = CommonTools.null2String(rest.getString("family_code"));
    			reValue = model+"*"+quantity+"*"+familyCode ;
    		}
    		
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return reValue;
    }
    public String getPhasesCode(String manufacturingOrder,String groups) throws SelectException{
    	String phasesCode = "";
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select last_advance from pd_production where manufacturing_order ='"+manufacturingOrder+"' ");
    	sql.append(" and groups='"+groups.split("\\*")[0]+"' and ");	
        sql.append(" last_advance_date =(select MAX(last_advance_date) from pd_production where  ");
        sql.append(" manufacturing_order ='"+manufacturingOrder+"' and groups='"+groups.split("\\*")[0]+"')");
        ResultSet rest = null;
        try{
        	log.info(sql.toString());
        	
        	rest = this.executeQuery(sql.toString());
        	while(rest.next()){
        		phasesCode = CommonTools.null2String(rest.getString("last_advance"));
        	}
        }catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return phasesCode;
    }
    
    public String getModelName(String manufacturingOrder) throws SelectException{
    	String modelName = "";
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select a.model_type,b.name from t_not_standard_products a,t_product_model b ");
    	sql.append(" where a.manufacturing_order ='"+manufacturingOrder+"'" );
    	sql.append(" and a.model_type=b.code ");
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest= this.executeQuery(sql.toString());
    		while(rest.next()){
    			modelName = CommonTools.null2String(rest.getString("name"));
    		}
    		
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return modelName;
    }
    
    public List getPreviousAdvanceList(String manufacturingOrder) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select a.phases,a.code,a.serial from t_critical_phases a,t_family b,t_not_standard_products c ");
    	sql.append(" where c.manufacturing_order = '"+manufacturingOrder+"'");
    	sql.append(" and c.family_code = b.family_code ");
    	sql.append(" and b.phases_code = a.code order by a.serial ");
    	ResultSet rest = null;
    	Map map=null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			map = new HashMap();
    			map.put("CODE1", CommonTools.null2String(rest.getString("code")));
    			map.put("NAME1", CommonTools.null2String(rest.getString("serial")));
    			list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public List getGroupsListOther(String manufacturingOrder) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select groups from pd_planning ");
    	sql.append(" where manufacturing_order = '"+manufacturingOrder+"'");
    	sql.append(" order by groups ");
    	ResultSet rest = null;
    	Map map=null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			map = new HashMap();
    			map.put("CODE", rest.getString("groups"));
    			map.put("NAME", rest.getString("groups"));
    			list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    public List getGroupsList(String manufacturingOrder) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select groups,group_qty from pd_planning ");
    	sql.append(" where manufacturing_order = '"+manufacturingOrder+"'");
    	sql.append(" order by groups ");
    	ResultSet rest = null;
    	Map map=null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			map = new HashMap();
    			map.put("CODE", rest.getString("groups")+"*"+rest.getString("group_qty"));
    			map.put("NAME", rest.getString("groups"));
    			list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public List selectByIdOther(String id) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();    	
    	
    	sql.append(" SELECT a.id,a.manufacturing_order,a.groups,a.group_qty,a.purchase_flag,");
    	sql.append(" adyh.FUN_GETGROUPQTY(a.manufacturing_order) as allQty,a.quantity, ");
    	sql.append(" a.finish_date,a.start_date,a.family_code,a.description,b.model  ");
    	sql.append(" FROM pd_planning a,c_orders b ");
    	sql.append(" where a.id = "+id);
    	sql.append(" and a.manufacturing_order=b.manufacturing_order ");
    	ResultSet rest = null;
    	try {
    		log.info("sql is "+sql);
    		rest = this.executeQuery(sql.toString());
			while(rest.next()){
				Map map = new HashMap();
				map.put("ID", CommonTools.null2String(rest.getString("id")));
				map.put("MANUFACTURINGORDER", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("GROUPS", CommonTools.null2String(rest.getString("groups")));
				map.put("GROUPQTY", CommonTools.null2String(rest.getString("group_qty")));
				map.put("PURCHASEFLAG", CommonTools.null2String(rest.getString("purchase_flag")));
				map.put("ALLQTY", CommonTools.null2String(rest.getString("allQty")));
				map.put("QUANTITY", CommonTools.null2String(rest.getString("quantity")));
				map.put("FINISHDATE", CommonTools.getDateTime(rest.getDate("finish_date")));
				map.put("STARTDATE", CommonTools.getDateTime(rest.getDate("start_date")));
				map.put("FAMILYCODE", CommonTools.null2String(rest.getString("family_code")));
				map.put("DESCRIPTION", CommonTools.null2String(rest.getString("description")));
				map.put("MODEL", CommonTools.null2String(rest.getString("model")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Dispose.dispose(rest);
		}
		return list;
    }
    
    public List selectById(String id) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();    	
    	
    	sql.append("SELECT id,manufacturing_order,groups,group_qty,previous_advance,last_advance,last_advance_date,manufacturing_qty  FROM pd_production ");
    	sql.append(" where id = ").append(id);
    	
    	ResultSet rest = null;
    	try {
    		log.info("sql is "+sql);
    		rest = this.executeQuery(sql.toString());
			while(rest.next()){
				Map map = new HashMap();
				map.put("ID", CommonTools.null2String(rest.getString("id")));
				map.put("MANUFACTURINGORDER", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("GROUPS", CommonTools.null2String(rest.getString("groups")));
				map.put("GROUPQTY", CommonTools.null2String(rest.getString("group_qty")));
				map.put("PREVIOUSADVANCE", CommonTools.null2String(rest.getString("previous_advance")));
				map.put("LASTADVANCE", CommonTools.null2String(rest.getString("last_advance")));
				map.put("LASTADVANCEDATE", CommonTools.getDateTime(rest.getDate("last_advance_date")));
				map.put("MANUFACTURINGQTY", CommonTools.null2String(rest.getString("manufacturing_qty")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Dispose.dispose(rest);
		}
		return list;
    }
    //判断是否 工作号，最后一个组的，最后一个阶段
    public boolean judgeManufacturingFinally(String manufacturingOrder,String lastAdvance,String group,String groupQty){
    	boolean flag = false;
    	StringBuffer sql = new StringBuffer();
    	int percents = 0;
    	sql.append(" select a.percents from t_family a, t_not_standard_products b,");
    	sql.append(" pd_planning c  where a.family_code = b.family_code and ");
    	sql.append(" b.manufacturing_order = '"+manufacturingOrder+"' and a.phases_code = '"+lastAdvance+"' and ");
    	//sql.append(" c.groups = adyh.FUN_GETLASTGROUP(c.manufacturing_order) and ");
    	sql.append(" c.groups = '"+group+"' and b.manufacturing_order = c.manufacturing_order ");
    	sql.append(" and c.quantity = ((select sum(manufacturing_qty) from pd_production where ");
    	sql.append(" manufacturing_order = '"+manufacturingOrder+"')+"+groupQty+")");
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			percents = rest.getInt("percents");
    		}
    		if(percents==100){
    			flag = true;
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return flag;
    }
    
    //判断是否 此工作号最后一个阶段
    public boolean judgeManufacturingQty(String manufacturingOrder,String lastAdvance) throws SelectException{
    	boolean flag = false;
    	StringBuffer sql = new StringBuffer();
    	int percents =0;
    	sql.append(" select a.percents from t_family a,t_not_standard_products b ");
    	sql.append(" where a.family_code = b.family_code and b.manufacturing_order ='"+manufacturingOrder+"'");
    	sql.append(" and a.phases_code ="+lastAdvance );
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			percents = rest.getInt("percents");
    		}
    		if(percents==100){
    			flag = true;
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return flag;
    }
    
    public boolean update(F0301ActionForm form){
    	boolean flag =false;
    	StringBuffer sql=new StringBuffer();
		List sqlList = new ArrayList();
		sql.append("update pd_planning set ");
		sql.append(" group_qty="+form.getGroupQty());
		sql.append(" ,start_date=convert(datetime,'"+form.getStartDate()+"')");
		sql.append(" ,finish_date=convert(datetime,'"+form.getFinishDate()+"')");
		sql.append(" ,purchase_flag='"+form.getPurchaseFlag()+"'");
		sql.append(" ,description='"+form.getDescription()+"'");
		sql.append(" where id="+form.getID());
		sqlList.add(sql.toString());
		try{
			log.info("sql is "+sql.toString());
			flag=super.executeUpdate(sqlList,true); 	
			form.setErrorFlag("保存成功!!!");
		}catch(Exception e){
	        e.printStackTrace();
	        form.setErrorFlag("保存失败!!!");
		}
		return flag;
    }
    
    public boolean update(PdPlanningInfo info,F0302ActionForm form){
    	boolean flag =false;
    	StringBuffer sql=new StringBuffer();
		List sqlList = new ArrayList();
		int manufacturingQty = 0;
		boolean finishFlag = false;
		StringBuffer sql1 = new StringBuffer();
		
		sql.append("update pd_production set ");
		sql.append(" manufacturing_order='"+info.getManufacturingOrder()+"',");
		sql.append(" previous_advance= '"+info.getPreviousAdvance()+"'");
		sql.append(" ,groups='"+info.getGroups()+"'");
		sql.append(" ,group_qty="+info.getGroupQty());
		sql.append(" ,last_advance='"+info.getLastAdvance()+"'");
		sql.append(" ,last_advance_date=convert(datetime,'"+info.getLastAdvanceDate()+"')");
		sql.append(" where id="+info.getId());
		sqlList.add(sql.toString());
		try{
			finishFlag = judgeManufacturingQty(info.getManufacturingOrder(),info.getLastAdvance());
			if(finishFlag){
				manufacturingQty = CommonTools.getIntNull(info.getGroupQty());
				sql1.append("update pd_finish_product set ");
				sql1.append(" manufacturing_order='"+info.getManufacturingOrder()+"',groups='"+info.getGroups()+"'," );
				sql1.append(" qty="+manufacturingQty+",stock_date=convert(datetime,'"+info.getLastAdvanceDate()+"') ");
				sql1.append(" where manufacturing_order='"+info.getManufacturingOrder()+"' and groups='"+info.getGroups()+"'");
				sqlList.add(sql1.toString());
			}
			log.info("sql is "+sql.toString());
			flag=super.executeUpdate(sqlList,true); 	
			form.setErrorFlag("0");
		}catch(Exception e){
	        e.printStackTrace();
	        form.setErrorFlag("1");
		}
		return flag;
    }
    
    public boolean delete(String id,F0302ActionForm form){
		boolean flag=true;
		StringBuffer sql=new StringBuffer();
		StringBuffer sql1 = new StringBuffer();
		StringBuffer sql2 = new StringBuffer();
		List sqlList = new ArrayList();
		sql.append("DELETE FROM pd_production  ");
		sql.append("WHERE id ="+id);
		sqlList.add(sql.toString());
		
		sql1.append(" delete from pd_finish_product ");
		sql1.append(" where manufacturing_order ='"+form.getManufacturingOrder()+"'");
		sql1.append(" and groups='"+form.getGroups()+"'");
		sqlList.add(sql1.toString());
		
		sql2.append(" update c_orders set state='1' ");
		sql2.append(" where manufacturing_order='"+form.getManufacturingOrder()+"'");
		sqlList.add(sql2.toString());
		try{
			log.info("sql is "+sql.toString());
			flag=super.executeUpdate(sqlList,true);
			form.setErrorFlag("0");
		}catch(Exception e){
			e.printStackTrace();
			form.setErrorFlag("1");
		}
		return flag;
	}
    
    public boolean delete(String id,F0301ActionForm form){
		boolean flag=true;
		StringBuffer sql=new StringBuffer();
		List sqlList = new ArrayList();
		sql.append("DELETE FROM pd_planning   ");
		sql.append("WHERE id ="+id);
		sqlList.add(sql.toString());
		try{
			log.info("sql is "+sql.toString());
			flag=super.executeUpdate(sqlList,true);
			form.setErrorFlag("0");
		}catch(Exception e){
			e.printStackTrace();
			form.setErrorFlag("1");
		}
		return flag;
	}
    
    public boolean insert(PdPlanningInfo info,F0302ActionForm form){
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		List sqlList = new ArrayList();
		int manufacturingQty = 0;
		boolean finishFlag = false;
		boolean finishGroupFlag = false;
		StringBuffer sql1 = new StringBuffer();
		StringBuffer sql2 = new StringBuffer();
		StringBuffer sql3 = new StringBuffer();
		try{
			//判断是否为 工作号中最后一个阶段
			finishGroupFlag = judgeManufacturingQty(info.getManufacturingOrder(),info.getLastAdvance());
			//判断是否为 工作号最后一组的最后一个阶段
			finishFlag = judgeManufacturingFinally(info.getManufacturingOrder(),info.getLastAdvance(),info.getGroups(),info.getGroupQty());
			if(finishGroupFlag){
				manufacturingQty = CommonTools.getIntNull(info.getGroupQty());
				sql1.append("insert into pd_finish_product (");
				sql1.append(" manufacturing_order,groups,qty,stock_date ) ");
				sql1.append(" values ('"+info.getManufacturingOrder()+"','"+info.getGroups()+"',"+manufacturingQty );
				sql1.append(" ,convert(datetime,'"+info.getLastAdvanceDate()+"') )");
				sqlList.add(sql1.toString());
	
				if(finishFlag){
					sql3.append(" update c_orders set state ='2' ");
					sql3.append(" where manufacturing_order = '"+info.getManufacturingOrder()+"'");
					sqlList.add(sql3.toString());
				}
			}
			
			sql.append("INSERT INTO pd_production  ");
			sql.append("  (manufacturing_order, groups, group_qty,previous_advance,last_advance,last_advance_date,manufacturing_qty ) ");
			sql.append("VALUES ('"+info.getManufacturingOrder()+"', '"+info.getGroups()+"', "+info.getGroupQty());
			sql.append(" ,'"+info.getPreviousAdvance()+"','"+info.getLastAdvance()+"',convert(datetime,'"+info.getLastAdvanceDate()+"'),"+manufacturingQty +")");
			sqlList.add(sql.toString());
			
			sql2.append(" update pd_planning set purchase_flag ='0' ");
			sql2.append(" where manufacturing_order='"+info.getManufacturingOrder()+"'");
			sql2.append(" and groups ="+info.getGroups());
			sqlList.add(sql2.toString());
			
			log.info("sql is "+sql.toString());
			log.info("sql2 is "+sql2.toString());
			flag=super.executeUpdate(sqlList,true); 
			form.setErrorFlag("0");
		}catch(SQLException ex){
			if("1".equals(String.valueOf(ex.getErrorCode()))){
				//form.setErrorFlag("主键重复,错误序号: "+String.valueOf(ex.getErrorCode()));
				form.setErrorFlag("3");
			}else{
				form.setErrorFlag("1");
			}
		}catch(Exception e){
			e.printStackTrace();
			form.setErrorFlag("1");
		}
		return flag;
	}
    
    public List queryTotalList(PdPlanningInfo info) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	
    	sql.append(" select sum(a.group_qty) AS total_group_qty, ");
    	sql.append(" sum(convert(float,l.unit_price)) as total_unit_price, ");
    	sql.append(" sum(CONVERT(float,l.total_price)) as total_total_price, ");
    	sql.append(" sum(convert(float,adyh.FUN_GETMATERIALCAST(b.serial,c.code))) as total_unit_cost_price, ");
    	sql.append("  sum(CONVERT(float,(adyh.FUN_GETMATERIALCAST(b.serial,c.code)*group_qty))) as total_total_cost_price ");
    	sql.append(" from pd_production a,t_family b,t_not_standard_products c, ");
        sql.append("  t_product_model i, c_orders l  ");
        sql.append(" where a.manufacturing_order = c.manufacturing_order ");
        if(!info.getManufacturingOrder1().equals("")){
        	sql.append(" and a.manufacturing_order >='"+info.getManufacturingOrder1()+"' ");
        }
        if(!info.getManufacturingOrder2().equals("")){
        	
        	sql.append("  and a.manufacturing_order <='"+info.getManufacturingOrder2()+"' ");
        }
        sql.append(" and a.last_advance = b.phases_code and ");
        sql.append(" b.family_code = c.family_code and ");
        sql.append(" c.model_type = i.code and ");
        sql.append(" l.manufacturing_order = a.manufacturing_order and ");
        sql.append("  b.serial = adyh.FUN_GETMAXSERIAL(a.manufacturing_order,'"+info.getLastAdvanceDate1()+"','"+info.getLastAdvanceDate2()+"')  ");

    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
				map.put("totalGroupQty", CommonTools.null2String(rest.getString("total_group_qty")));
				map.put("totalUnitPrice", CommonTools.convertCurrencyRMB(rest.getString("total_unit_price")));
				map.put("totalTotalPrice", CommonTools.convertCurrencyRMB(rest.getString("total_total_price")));
				map.put("totalUnitCostPrice", CommonTools.convertCurrencyRMB(rest.getString("total_unit_cost_price")));
				map.put("totalTotalCostPrice", CommonTools.convertCurrencyRMB(rest.getString("total_total_cost_price")));
				
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    public List queryPdPlanningReportList(F0303ActionForm actionForm) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	
    	sql.append(" select h.manufacturing_order,h.groups,h.group_qty,h.quantity,h.family_code,");
    	sql.append(" h.model,");
    	sql.append(" j.phases,j.serial,j.begin_date,j.end_date from "); 
    	sql.append(" (select e.manufacturing_order,e.groups,e.group_qty,e.quantity,e.family_code,");  
    	sql.append(" d.model "); 
    	sql.append(" from pd_planning e,c_orders d "); 
		sql.append(" where e.manufacturing_order = d.manufacturing_order) h, "); 
		
		sql.append(" (select a.family_code,b.phases,a.serial,c.groups,");  
		sql.append("  adyh.FUN_GETGROUPBEGINDATE(a.family_code,a.serial,c.finish_date) as begin_date,"); 
		sql.append(" adyh.FUN_GETGROUPENDDATE(a.family_code,a.serial,c.finish_date) as end_date"); 
		sql.append(" from t_family a,t_critical_phases b,pd_planning c"); 
		sql.append(" where c.family_code = a.family_code and ");  
		sql.append(" a.phases_code = b.code"); 
		sql.append(" group by a.family_code,a.serial,b.phases,c.finish_date,c.groups");  
		sql.append(" ) j"); 
		sql.append(" where h.family_code = j.family_code and h.groups = j.groups "); 
		
		if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and h.manufacturing_order >='"+actionForm.getManufacturingOrder1()+"' ");
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		 sql.append(" and h.manufacturing_order <='"+actionForm.getManufacturingOrder2()+"' ");
    	}
    	if(!actionForm.getStartDate1().equals("")){
    		sql.append(" and j.begin_date >= '"+actionForm.getStartDate1()+"'");
    	}
    	if(!actionForm.getStartDate2().equals("")){
    		sql.append(" and j.begin_date <= '"+actionForm.getStartDate2()+"'");
    	}
    	if(!actionForm.getFinishDate1().equals("")){
    		sql.append(" and j.end_date >= '"+actionForm.getFinishDate1()+"'");
    	}
    	if(!actionForm.getFinishDate2().equals("")){
    		sql.append(" and j.end_date <= '"+actionForm.getFinishDate2()+"'");
    	}
		sql.append(" order by h.family_code,h.groups,j.serial"); 
		
    	ResultSet rest = null;
    	String quantity="";
    	String manufacturingOrderCycle ="";
    	String groupsCycle ="";
    	String manufacturingOrder ="";
    	String model="";
    	String groups="";
    	String groupQty="";
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			if(manufacturingOrderCycle.equals(CommonTools.null2String(rest.getString("manufacturing_order")))&&groupsCycle.equals(CommonTools.null2String(rest.getString("groups")))){
    				manufacturingOrder ="";
    				model ="";
    				quantity="";
    				groups="";
    				groupQty="";
    			}else{
    				manufacturingOrderCycle = CommonTools.null2String(rest.getString("manufacturing_order"));
    				groupsCycle =  CommonTools.null2String(rest.getString("groups"));
    				manufacturingOrder = CommonTools.null2String(rest.getString("manufacturing_order"));
    				model = CommonTools.null2String(rest.getString("model"));
    				quantity = CommonTools.null2String(rest.getString("quantity"));
    				groups = CommonTools.null2String(rest.getString("groups"));
    				groupQty = CommonTools.null2String(rest.getString("group_qty"));
    			}
    			
				map.put("manufacturingOrder", manufacturingOrder);
				map.put("model", model);
				map.put("quantity", quantity);
				map.put("groups", groups);
				map.put("groupQty", groupQty);
				map.put("phases", CommonTools.null2String(rest.getString("phases")));
				map.put("finishDate", CommonTools.getDateTime(rest.getDate("end_date")));
				map.put("startDate", CommonTools.getDateTime(rest.getDate("begin_date")));
				
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public List queryProductionReportList(PdPlanningInfo info) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	
    	sql.append(" select a.manufacturing_order,a.groups, a.group_qty,a.last_advance,adyh.FUN_GETPHASES(a.last_advance) as last_advance_name, ");
    	sql.append(" a.last_advance_date,i.name,l.unit_price,l.total_price, ");
    	sql.append(" (adyh.FUN_GETMATERIALCAST(b.serial,c.code)+adyh.FUN_GETHOURSCAST(b.serial,b.family_code)) as unit_cost_price, ");
    	sql.append(" ((adyh.FUN_GETMATERIALCAST(b.serial,c.code)+adyh.FUN_GETHOURSCAST(b.serial,b.family_code))*group_qty) as total_cost_price ");
    	sql.append("  from pd_production a,t_family b,t_not_standard_products c,");
    	sql.append(" t_product_model i, c_orders l");
    	sql.append(" where a.manufacturing_order = c.manufacturing_order ");
    	if(!info.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+info.getManufacturingOrder1()+"' ");
    	}
    	if(!info.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+info.getManufacturingOrder2()+"' ");
    	}
        sql.append(" and a.last_advance = b.phases_code and ");
        sql.append(" b.family_code = c.family_code and ");
        sql.append(" c.model_type = i.code and ");
        sql.append(" l.manufacturing_order = a.manufacturing_order and ");
        sql.append(" b.serial = adyh.FUN_GETMAXSERIAL(a.manufacturing_order,'"+info.getLastAdvanceDate1()+"','"+info.getLastAdvanceDate2()+"')  ");
 
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
				map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("groups", CommonTools.null2String(rest.getString("groups")));
				map.put("groupQty", CommonTools.null2String(rest.getString("group_qty")));
				map.put("lastAdvance", CommonTools.null2String(rest.getString("last_advance")));
				map.put("lastAdvanceName", CommonTools.null2String(rest.getString("last_advance_name")));
				map.put("lastAdvanceDate", CommonTools.getDateTime(rest.getDate("last_advance_date")));
				map.put("model", CommonTools.null2String(rest.getString("name")));
				map.put("unitPrice", CommonTools.convertCurrencyRMB(rest.getString("unit_price")));
				map.put("totalPrice", CommonTools.convertCurrencyRMB(rest.getString("total_price")));
				map.put("unitCostPrice", CommonTools.convertCurrencyRMB(rest.getString("unit_cost_price")));
				map.put("totalCostPrice", CommonTools.convertCurrencyRMB(rest.getString("total_cost_price")));
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public List queryList(F0301ActionForm actionForm) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	String quantity="";
    	String manufacturingOrderCycle ="";
    	String manufacturingOrder ="";
    	String model="";
    	String familyCode="";
    	sql.append(" select a.id,a.manufacturing_order,b.model,a.groups,a.group_qty, ");
    	sql.append(" a.finish_date,a.start_date,a.purchase_flag,c.name,a.family_code, ");
    	sql.append(" a.description,a.quantity ");
    	sql.append(" from pd_planning a, c_orders b,b_whether c");
    	sql.append("  where a.manufacturing_order = b.manufacturing_order and c.code=a.purchase_flag "); 
    	if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+actionForm.getManufacturingOrder1()+"' ");
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+actionForm.getManufacturingOrder2()+"' ");
    	}
    	if(!actionForm.getStartDate1().equals("")){
    		sql.append(" and a.start_date >=convert(datetime,'"+actionForm.getStartDate1()+"')");
    	}
    	if(!actionForm.getStartDate2().equals("")){
    		sql.append(" and a.start_date <=convert(datetime,'"+actionForm.getStartDate2()+"')");
    	}
    	if(!actionForm.getFinishDate1().equals("")){
    		sql.append(" and a.finish_date >=convert(datetime,'"+actionForm.getFinishDate1()+"')");
    	}
    	if(!actionForm.getFinishDate2().equals("")){
    		sql.append(" and a.finish_date <=convert(datetime,'"+actionForm.getFinishDate2()+"')");
    	}
        sql.append(" order by a.manufacturing_order ");
        
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			if(manufacturingOrderCycle.equals(CommonTools.null2String(rest.getString("manufacturing_order")))){
    				manufacturingOrder ="";
    				model ="";
    				familyCode="";
    				quantity="";
    			}else{
    				manufacturingOrderCycle = CommonTools.null2String(rest.getString("manufacturing_order"));
    				manufacturingOrder = CommonTools.null2String(rest.getString("manufacturing_order"));
    				model = CommonTools.null2String(rest.getString("model"));
    				familyCode = CommonTools.null2String(rest.getString("family_code"));
    				quantity = CommonTools.null2String(rest.getString("quantity"));
    			}
    			
				map.put("manufacturingOrder", manufacturingOrder);
				map.put("model", model);
				map.put("familyCode", familyCode);
				map.put("quantity", quantity);
				map.put("ID",CommonTools.null2String(rest.getString("id")));
				map.put("groups", CommonTools.null2String(rest.getString("groups")));
				map.put("groupQty", CommonTools.null2String(rest.getString("group_qty")));
				map.put("finishDate", CommonTools.getDateTime(rest.getDate("finish_date")));
				map.put("startDate", CommonTools.getDateTime(rest.getDate("start_date")));
				map.put("purchaseFlag", CommonTools.null2String(rest.getString("name")));
				map.put("description", CommonTools.null2String(rest.getString("description")));
				
				list.add(map);
    		}
    		if(list.size()==0){
    			actionForm.setErrorFlag("2");
    		}
    		
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public List queryList(PdPlanningInfo info) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append("SELECT a.id,a.manufacturing_order,a.manufacturing_qty,a.groups,a.group_qty,a.previous_advance,a.last_advance,a.last_advance_date ,c.name,adyh.FUN_GETPHASES(a.previous_advance) previous_advance_name,adyh.FUN_GETPHASES(a.last_advance) last_advance_name ") ;
    	sql.append(" ,d.serial,adyh.FUN_GETMAXSERIALGROUPS(a.manufacturing_order,a.groups) max_serial ");
    	sql.append(" FROM pd_production a,t_not_standard_products b,t_product_model c, t_family d ");
    	sql.append(" where a.manufacturing_order=b.manufacturing_order and b.model_type=c.code  ");
    	sql.append(" and b.family_code = d.family_code and a.last_advance=d.phases_code ");
    	if(!info.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >="+info.getManufacturingOrder1());
    	}
    	if(!info.getManufacturingOrder2().equals("")){
    		sql.append(" and a.manufacturing_order <="+info.getManufacturingOrder2());
    	}
    	sql.append(" order by a.manufacturing_order,a.groups, d.serial ");
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			map.put("id", CommonTools.null2String(rest.getString("id")));
				map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("manufacturingQty", CommonTools.null2String(rest.getString("manufacturing_qty")));
				map.put("groups", CommonTools.null2String(rest.getString("groups")));
				map.put("groupQty", CommonTools.null2String(rest.getString("group_qty")));
				map.put("previousAdvance", CommonTools.null2String(rest.getString("previous_advance")));
				map.put("lastAdvance", CommonTools.null2String(rest.getString("last_advance")));
				map.put("lastAdvanceDate", CommonTools.getDateTime(rest.getDate("last_advance_date")));
				map.put("model", CommonTools.null2String(rest.getString("name")));
				map.put("lastAdvanceName", CommonTools.null2String(rest.getString("last_advance_name")));
				map.put("previousAdvanceName", CommonTools.null2String(rest.getString("previous_advance_name")));
				map.put("serial", CommonTools.null2String(rest.getString("serial")));
				map.put("maxSerial", CommonTools.null2String(rest.getString("max_serial")));
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public boolean SAVE_INSERT(F0301ActionForm actionForm,Map map,int length) {
		boolean falg = false;
		List sqlList = new ArrayList();				
		/** 获得操作记录SQL */
		StringBuffer msql = new StringBuffer();		
		try {
			for(int i=0;i<length;i++){
				msql.append("INSERT INTO pd_planning( ");		
				msql.append("manufacturing_order,quantity,family_code,purchase_flag,");
				msql.append("groups, group_qty,start_date,finish_date,description ");
				msql.append(" )VALUES ( ");				
				msql.append("'" + actionForm.getManufacturingOrder() + "',"+actionForm.getQuantity());
				msql.append(" ,'"+actionForm.getFamilyCode()+"','1',"+map.get("1"+i)+","+map.get("2"+i));	
				msql.append(",convert(datetime,'" + map.get("3"+i) + "'), ");
				msql.append("convert(datetime,'" + map.get("4"+i) + "'), ");
				msql.append("'" + map.get("5"+i) + "' )");
				sqlList.add(msql.toString());
				
				msql.delete(0, msql.length());			
			}
			falg = super.executeUpdate(sqlList, true);
			
			actionForm.setErrorFlag("0");/**成功*/
		} catch (SQLException ex) {
			if ("1".equals(String.valueOf(ex.getErrorCode()))) {
				/** ex.getErrorCode()返回一，表示主键冲突 */
				actionForm.setErrorFlag("1");/** 主键冲突! */
			} else {
				actionForm.setErrorFlag("1");/**保存失败*/
			}
		} catch (Exception e) {
			actionForm.setErrorFlag("4");/**  */
			e.printStackTrace();
			super.rollback();

		}
		super.commit();

		return falg;
	}
}
